﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for SubInvoiceDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class HOA_DON_CT_DAO
    {
        private const string SQL_GEN_SUB_INVOICE = "EXEC genSubInvoice(@MA_HOA_DON, @NGAY_KICH_HOAT, @DANG_KY_NHOM)";
        private const string SQL_UPDATE = "update HOA_DON_CT set TRANG_THAI = @TRANG_THAI where MA_HOA_DON_CT = @HOA_DON_CT";
        private const string SQL_UPDATE_RULE_FEE_DATE = "EXEC updateRule NGAY_THU_PHI(@MA_HOA_DON, @NGAY_KICH_HOAT, @CurrentDate)}";
        private const string SQL_SELECT_BY_INVOICE = "select * from HOA_DON_CT where MA_HOA_DON = @MA_HOA_DON order by NGAY_THU_PHI asc";

        // Parameters
        private const string PARM_INVOICE_ID = "@MA_HOA_DON";
        private const string PARM_INVOICE_ACTIVATED_DATE = "@NGAY_KICH_HOAT";
        private const string PARM_INVOICE_REGISTER_IN_GROUP = "@DANG_KY_NHOM";
        private const string PARM_SUB_INVOICE_ID = "@HOA_DON_CT";
        private const string PARM_SUB_INVOICE_IS_PAID = "@TRANG_THAI";
        private const string PARM_CURRENT_DATE = "@CurrentDate";

        #region Public Methods
        public IList<HOA_DON_CT> GetSubInvoicesByInvoice(int MA_HOA_DON)
        {
            IList<HOA_DON_CT> subInvoices = new List<HOA_DON_CT>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int)};
            parms[0].Value = MA_HOA_DON;

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_INVOICE, parms))
            {
                while (reader.Read())
                {
                    HOA_DON_CT subInvoice = new HOA_DON_CT();

                    subInvoice.MA_HOA_DON = new HOA_DON();
                    
                    subInvoice.MA_HOA_DON_CT = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON_CT"));
                    subInvoice.MA_HOA_DON.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    subInvoice.SO_TIEN = (float)reader.GetDouble(reader.GetOrdinal("SO_TIEN"));
                    subInvoice.NGAY_THU_PHI = reader.GetDateTime(reader.GetOrdinal("NGAY_THU_PHI"));
                    subInvoice.TRANG_THAI = reader.GetBoolean(reader.GetOrdinal("TRANG_THAI"));
                 if(!reader.IsDBNull(5))   subInvoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    subInvoices.Add(subInvoice);
                }
            }
            return subInvoices;
        }

        public Boolean GenSubInvoices(HOA_DON_CT subInvoice)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter("@invoiceId", SqlDbType.Int),
											 new SqlParameter("@activatedDate", SqlDbType.DateTime),
                                             new SqlParameter("@group", SqlDbType.Int)};
                parms[0].Value = subInvoice.MA_HOA_DON.MA_HOA_DON;
                parms[1].Value = subInvoice.MA_HOA_DON.NGAY_KICH_HOAT;
                parms[2].Value = subInvoice.MA_HOA_DON.DANG_KY_NHOM;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.StoredProcedure, "genSubInvoice", parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(HOA_DON_CT subInvoice)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_SUB_INVOICE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_SUB_INVOICE_IS_PAID, SqlDbType.Int)};
            parms[0].Value = subInvoice.MA_HOA_DON_CT;
            parms[1].Value = subInvoice.TRANG_THAI;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool UpdateRuleNGAY_THU_PHI(HOA_DON invoice, DateTime registerDate, DateTime currentDate)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                             new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int),
											 new SqlParameter(PARM_INVOICE_ACTIVATED_DATE, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CURRENT_DATE, SqlDbType.DateTime)};
            parms[0].Value = invoice.MA_HOA_DON;
            parms[1].Value = registerDate;
            parms[1].Value = currentDate;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE_RULE_FEE_DATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}